package cn.lanqiao.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;

import cn.lanqiao.dao.ScoreDao;
import cn.lanqiao.entity.Score;
import cn.lanqiao.util.JdbcUtil;

public class ScoreDaoImpl implements ScoreDao {

	@Override
	public Object[][] getAllScores() {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps =null;
		ResultSet rs =null;
		int count = this.count();
		Object[][] datas =new Object[count][] ;
		String sql="select s.sid,sname,cname,sgrade from edu_score s,edu_course c,edu_student t where s.sid=t.sid and s.scourse=c.cid";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			for(int i=0;rs.next();i++) {
				datas[i] =new Object[] {rs.getInt("sid"),rs.getString("sname"),rs.getString("cname"),rs.getString("sgrade")};
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return datas;
	}

	@Override
	public int count() {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps =null;
		ResultSet rs =null;
		String sql="select count(sgrade) from edu_score s,edu_course c,edu_student t where s.sid=t.sid and s.scourse=c.cid";
		int num = 0;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return num;
	}
	
	@Override
	public void deleteUserByid(String id) {
		Connection conn=JdbcUtil.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="delete from edu_score where sid=?";		
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, id);
			rs=ps.executeQuery();
			
			
		} catch (SQLException e) {

			e.printStackTrace();
		}finally {
			JdbcUtil.close(rs, ps, conn);
		}
	}

//添加数据
	@Override
	public int addScores(Score score) {
		Connection conn=JdbcUtil.getConnection();
		PreparedStatement ps=null;

		int values=0;
		String sql="insert into edu_score values (?,?,?,?)";
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,UUID.randomUUID().toString());
			ps.setString(2, score.getSid());
			ps.setString(3, score.getScourse());
			ps.setString(4, score.getSgrade());
			
			ps.executeUpdate();
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(null, ps, conn);
		}
			
		return values;
	}
//修改数据
	@Override
	public void update(String id,Score score) {
		Connection conn=JdbcUtil.getConnection();
		PreparedStatement ps=null;
		//找到修改的元素
		String sql="update edu_score set sgrade=? where sid=?";
		
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, score.getSgrade());
			ps.setString(2, id);
			ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(null, null, conn);
		}
	}
	
	@Override
	public Object[][] getAllScore() {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps =null;
		ResultSet rs =null;
		int count = this.counts();
		Object[][] datas =new Object[count][] ;
		String sql="select * from edu_score";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			for(int i=0;rs.next();i++) {
				datas[i] =new Object[] {rs.getString(2),rs.getString(3),rs.getString(4)};
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return datas;
	}

	@Override
	public int counts() {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps =null;
		ResultSet rs =null;
		String sql="select count(*) from edu_score";
		int num = 0;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return num;
	}

}
